<?php

//header( 'Content-Type: text/html; charset=utf-8' );
header( 'Content-Type: application/json; charset=utf-8' );

require_once ("../conf/conf.php");
require_once ("../locales/ro.php");
require ("../include/constants.php");
require ("../include/function.php");

$connection = db_connect(DB_HOST, DB_NAME, DB_USER, DB_PASSWORD);

$action = isset ($_POST['action']) ? $_POST['action'] : (isset($_GET['action']) ? $_GET['action'] : "get_chapter");
session_start();

if($action == 'get_chapter'){
    echo getChapter();
} else if($action == 'edit'){ // edit comments
    if(isset($_SESSION['user_id'])) {
        echo editComment();
    } else {
        echo renderUnauthorized();
    }
} else if($action == 'search'){ // edit comments
    echo search();
} else {
	echo renderExpectationFailed();
}

db_disconnect($connection);


// ====================================================
function getChapter(){
    $book = (int)$_GET['book'];
	$chapter = (int)$_GET['chapter'];
	// TODO convert to boolean
    $ref = $_GET['references'];
    if ( isset ( $_SESSION['user_id'] ) ) {
        $userId = $_SESSION['user_id'];
    }

    //$ref = (boolean)$ref;

	$sql = "SELECT verse.id, chapter.number, verse.number, text ";

    // bring titles in js even don't display them (to have bold verse nr)
    $sql = $sql.", (SELECT text FROM titluri WHERE id_verset = verse.id) AS title ";

    if($ref == 'true') {
        // '#' will be replaced with '<span class="ref" ext:qwidth="200" ext:qtip=" " ext:qurl="servlets/verse.php?id='
        $sql = $sql.", (SELECT GROUP_CONCAT(CONCAT('#',id_trimitere,'\">', text, '$') SEPARATOR '; ') ".
                "FROM trimiteri WHERE id_verset = verse.id GROUP BY id_verset) AS ref ";
    } else {
        $sql = $sql.", '' AS ref ";
    }

    if(isset($userId)) {
        $sql = $sql.", cm.comment, cm.bgcolor, cm.keyverse ";
    }

	$sql = $sql."FROM verse JOIN chapter ON (verse.chapter_id = chapter.id) ";

    if(isset($userId)) {
        $sql = $sql." LEFT JOIN verse_comments AS cm ON (verse.id = cm.verset_id AND cm.user_id = $userId) ";
    }

    $sql = $sql."WHERE book_id = $book AND chapter.number = $chapter ";

	$records = sql2JSONArray($sql);

    $rez = jsonRecords($records);
    $theme = '___ setaţi tema capitolului ' . $chapter . ' ___';
    if(isset($userId)) {
        $data = getUserBookTheme($userId, $book, $chapter);
    }
    $rez["theme"] = isset($data) ? $data[1] : $theme;

    return json_encode($rez);
}

// ====================================================
function editComment(){
    $userId = $_SESSION['user_id'];
    $id = (int)$_POST['id'];

    // TODO verify $value for SQL Injection
    $value = trim($_POST['value']);
//    $value = mysql_real_escape_string(trim($_POST['value']));
    // TODO verify $field for SQL Injection
    $field = trim($_POST['field']);
//    $field = mysql_real_escape_string(trim($_POST['field']));

    if(in_array($field, array("comment", "bgcolor", "keyverse"))){
        $sql = "SELECT id, comment, bgcolor, keyverse FROM verse_comments WHERE verset_id = $id AND user_id = $userId";
        $result = do_query($sql);
        if(mysql_num_rows($result)) { //See if there is anything in the query
            if($data = mysql_fetch_row($result)) {

                // IF comment is blank remove it from DB
//                if($comment == ''){
//                    $sql = "DELETE FROM verse_comments WHERE verset_id = $id AND user_id = $userId";
//                    $r = do_query($sql);
//                    if($r == 1){
//                        return "OK";
//                    }
//                }

                $sql = "UPDATE verse_comments set $field = '$value' WHERE id = ".$data[0];
                $r = do_query($sql);
                $i = mysql_affected_rows();
                if($i == 1){
                    return renderSuccess();
                }
            }
        } else {
            $sql = "INSERT INTO verse_comments($field, user_id, verset_id) VALUES('$value', $userId, $id)";
            $r = do_query($sql);
            if($r == 1){
                return renderSuccess();
            }
        }
    }

    return renderError("Update Error"); // TODO i18n
}

// ====================================================
function search(){
    $type = $_GET['type'];
    $dir = $_GET['dir'];

    // TODO verify $query for SQL Injection
    //$query = preg_quote($_GET['query']); // like Ext.escapeRe
//    $query = mysql_real_escape_string($_GET['query']); // like Ext.escapeRe
    $query = $_GET['query']; // like Ext.escapeRe

    $start = (int)$_GET['start'];
    $limit = (int)$_GET['limit'];
    $book = (int)$_GET['book'];

    $total = 0;
    if(!empty($query)){
        $sql = "SELECT verse.id, chapter.book_id, chapter.number, verse.number, text ".
                "FROM verse JOIN chapter ON (verse.chapter_id = chapter.id) WHERE ";
        $where = "";
        if($book){
            $where = "chapter.book_id = $book AND ";
        }
        if($type == 'all'){
            $query = split(' ', $query); // TODO use preg_split
            $where .= "(";
            foreach($query as $q) {
                $where .= "text LIKE '%$q%' AND ";
                // $where .= "(text LIKE '$q%' OR text LIKE '% $q%') AND ";
            }
            $where .= "1 = 1) ";
        } else if($type == 'any'){
            $query = split(' ', $query); // TODO use preg_split
            $where .= "(";
            foreach($query as $q) {
                $where .= "text LIKE '%$q%' OR ";
            }
            $where .= "1 = 0) ";
        } else {
            $where .= "text LIKE '%$query%' ";
        }
        $sql .= "$where ";

        $dir = $dir == 'ASC' ? 'ASC' : 'DESC'; // for security
        $sql .= "ORDER BY verse.id $dir ";

        $sql .= "LIMIT $start, $limit";
        $records = sql2JSONArray($sql);

        if($book){
            $sqlCount = "SELECT count(*) total FROM verse JOIN chapter ON (verse.chapter_id = chapter.id) WHERE $where";
        } else {
            $sqlCount = "SELECT count(*) total FROM verse WHERE $where";
        }

        $result = do_query($sqlCount);
        $total = mysql_fetch_array($result);
        $total = $total[0];
    } else {
        $records = array();
    }

    $rez = jsonRecords($records);
    $rez["total"] = $total;
    return json_encode($rez);
}



?>